The present invention relates to the field of database replication and, more particularly, to an automated means for restoring data replication consistency without service (i.e., replication and/or transaction processing services) interruption during parallel apply.
In a relational database management system (RDMBS), data is stored in many tables having multiple rows, which each having multiple columns. Frequently, copies (or replicas) of a first table within an RDBMS are made to other RDBMS. The propagation of changes made to one database may occur synchronously or asynchronously. Replicating transactions can change severable tables, but not all tables are necessarily replicated. In a situation where all tables are intended for replication (i.e., subscribed) then the transaction is replicated entirely. For the purpose of embodiments of the disclosure, changes need to be dealt with at the table level within each replicated transaction. Depending on context database and table level replication is referred to herein. Asynchronous propagation copies original changes from a first database table to one or more other tables, subsequent to a completion of a transaction that makes changes. Often asynchronous replication is preferred for low-overhead, and parallel apply of changes is preferred for efficiency, which imposes some challenges with regard to data constraints on a table and referential integrity across tables. For example, changes to a first table can occur while changes to the table are being replicated. The complexity of the challenges that occur with RDBMS table replication increases when a highly optimized technique is being used, such as parallel apply.
Parallel apply processing improves performance of a table replication process by applying units of recovery (UOR) concurrently to target data sets. A number of known techniques exist and are in use in industry that preserve data constraints and referential integrity during parallel apply in asynchronous transaction replication in an RDBMS database, such as those detailed in U.S. Pat. No. 7,240,054 titled “Techniques To Preserve Data Constraints and Referential Integrity in Asynchronous Transactional Replication of Relational Tables” and U.S. Pat. No. 7,330,860 titled “Fault Tolerant Mechanism to Handle Initial Load Of Replicated Object In Live System” which are incorporated by reference herein. Existing parallel apply approaches are unable to handle data tables that have multiple unique constraints properties without issues occurring.
To elaborate, U.S. Pat. No. 7,240,054 (the '054 patent) and conforming approaches teach that secondary unique constraints are to be preserved when a constraint violations occur between at least one row change. The '054 patent provides a lock-free solution for achieving data consistency after an initial load of data, which includes changes and updates of the data that occur in parallel with the initial load. However, the '054 patents detailed technique can lead to unresolvable differences between a source and a target. With existing art and known techniques, initialization methods require users dropping all secondary unique indexes on a target until the source and target are synchronized. This dropping of secondary unique indexes requires a manual creation and running of scripts, which forces taking a target table offline for a period of time, often hours for very large tables. No known solution, applicable to a parallel apply environment is able to automatically restore database consistency when a violation occurs without taking the target table offline and performing manual actions, such as temporarily dropping secondary unique indexes.
To emphasize this point, U.S. Pat. No. 5,884,328 (the '328 patent) titled “System and Method For Synchronizing A Large Database and Its Replica” provides a technique that is unable to be implemented in the parallel apply environment. Specifically, the '328 patent is not applicable to a middleware based RDBMS replication environment where parallel apply is used. Middleware-based replication, from a perspective of the '328 patent, is unable to obtain an appropriate log-capture start LSN (“startlsn”) and max commit sequence (“maxcmtseq”). “Startlsn” refers to the log sequence number of the oldest uncommitted transaction that you want to capture. “Maxcmtseq” refers to the most recently committed transaction that was sent/applied to a target RDBMS. Under the '328 patent a replication engine is unable to detect and skip transactions that have been committed/included in an initial load copy. Thus, solutions such as those of the '328 patent that are dependent on values from “startlsn” and “maxcmtseq” are unhelpful in restoring database consistency in a parallel apply environment. No known solution outside the present disclosure exists, which is why conventionally manual efforts are required and which is why a target table is taken offline for a period of time in conventional implementations of highly parallel replication in presence of tables with multiple unique constraints.